package com.aliyun.heiyu.powermaster.server.database;

import com.aliyun.heiyu.common.util.JdbcUtil;
import com.aliyun.heiyu.common.util.StringUtil;
import com.aliyun.heiyu.powermaster.proto.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class OperaServiceImpl implements OperaService{
    private static Logger logger = LoggerFactory.getLogger(OperaServiceImpl.class);

    private DatabaseConfigDao databaseConfigDao;

    public OperaServiceImpl(){
        this.databaseConfigDao = new DatabaseConfigImpl();
    }

    public String convertData(int tag, String s)  {
        s = s.replace(":", "").replace("-", "").replace(" ", "");
        String result = "";
        int length = s.length();
        try {
            if(1 == tag && length < 14){
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyMMddHHmmss");
                Date parse = sdf2.parse(s);
                result = parse.toString();
            } else if(1 == tag){
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
                Date parse = sdf2.parse(s);
                result = parse.toString();
            }else if(2 == tag && length < 8){
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyMMdd");
                Date parse = sdf2.parse(s);
                result = parse.toString();
            } else if(2 == tag){
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");
                Date parse = sdf2.parse(s);
                result = parse.toString();
            }else if(3 == tag){
                SimpleDateFormat sdf2 = new SimpleDateFormat("HHmmss");
                Date parse = sdf2.parse(s);
                result = parse.toString();
            } else if(4 == tag){
                Integer integer = Integer.valueOf(s);
                result = integer == 0 ? "FALSE":"TRUE";
            } else {
                result = "数据格式不正确";
            }
        } catch (Exception e){
            logger.error("数据格式不正确，{}",e);
            result = "数据格式不正确";
        }
        return result;
    }

    public BaseReply insert(DatabaseConnectRequest request){
        BaseReply.Builder builder = BaseReply.newBuilder();
        String port = request.getPort();
        boolean ret = StringUtil.isPositiveNum(port);
        if(!ret){
            builder.setErrCode(702);
            builder.setErrMsg("新增失败，端口格式错误");
            return builder.build();
        }
        String name = request.getName();
        DatabaseConfig entity = databaseConfigDao.findByName(name);
        if(entity != null){
            builder.setErrCode(701);
            builder.setErrMsg("名称已经存在");
            return builder.build();
        }
        DatabaseConfig config = new DatabaseConfig();
        config.setName(name);
        config.setHost(request.getHost());
        config.setPort(String.valueOf(request.getPort()));
        config.setUsername(request.getUsername());
        config.setPa(request.getPassword());
        boolean insert = databaseConfigDao.insert(config);
        if(!insert){
            builder.setErrCode(702);
            builder.setErrMsg("新增失败");
        } else {
            builder.setErrCode(0);
            builder.setErrMsg("新增成功");
        }
        return builder.build();
    }

    public BaseReply delete(RemoveDatabaseConnectRequest request){
        BaseReply.Builder builder = BaseReply.newBuilder();
        String name = request.getName();
        String[] split = name.split(",");
        int length = split.length;
        boolean ret = true;
        if(length > 0){
            for(int i=0; i<length; i++){
                ret &= databaseConfigDao.deleteByName(split[i]);
                if(!ret){
                    break;
                }
            }
        }
        if(!ret){
            builder.setErrCode(702);
            builder.setErrMsg("删除失败");
        } else {
            builder.setErrCode(0);
            builder.setErrMsg("删除成功");
        }
        return builder.build();
    }

    public ConnectionReply getConnections1(){
        ConnectionReply.Builder builder = ConnectionReply.newBuilder();
        List<DatabaseConfig> all = databaseConfigDao.findAll();
        if(all == null){
            builder.setErrCode(600);
            builder.setErrMsg("系统异常");
            return builder.build();
        }
        builder.setErrCode(0);
        builder.setErrMsg("操作成功");
        ConnectionReply.Data.Builder data = ConnectionReply.Data.newBuilder();
        for(DatabaseConfig databaseConfig : all){
            ConnectionReply.Connection.Builder connectionBuilder = ConnectionReply.Connection.newBuilder();
            connectionBuilder.setName(databaseConfig.getName());
            connectionBuilder.setHost(databaseConfig.getHost());
            connectionBuilder.setPort(Integer.valueOf(databaseConfig.getPort()));
            connectionBuilder.setUsername(databaseConfig.getUsername());
            Connection connection = getConnection(databaseConfig.getName());
            if(connection == null){
                data.addConnections(connectionBuilder);
                continue;
            }
            List<String> databases = getDatabases(connection);
            if(databases == null){
                data.addConnections(connectionBuilder);
                continue;
            }
            for(String s : databases){
                ConnectionReply.Database.Builder databaseBuilder = ConnectionReply.Database.newBuilder();
                databaseBuilder.setName(s);
                Connection connection1 = getConnection(databaseConfig.getName());
                if(connection1 == null){
                    connectionBuilder.addChildren(databaseBuilder);
                    continue;
                }
                List<String> tables = getTables(connection1, s);
                if(tables == null){
                    connectionBuilder.addChildren(databaseBuilder);
                    continue;
                }
                for(String s2 : tables){
                    ConnectionReply.Table.Builder builder1 = ConnectionReply.Table.newBuilder();
                    builder1.setName(s2);
                    databaseBuilder.addChildren(builder1);
                }
                connectionBuilder.addChildren(databaseBuilder);
            }
            data.addConnections(connectionBuilder);
        }
        builder.setData(data);
        return builder.build();
    }

    public GetDataReply getData(GetDataRequest request){
        GetDataReply.Builder builder = GetDataReply.newBuilder();
        String connectionName = request.getConnectionName();
        String database = request.getDatabase();
        Connection connection = getConnection(connectionName,database);
        if(connection == null){
            builder.setErrCode(703);
            builder.setErrMsg("连接MYSQL服务失败");
            return builder.build();
        }
        builder.setErrCode(0);
        builder.setErrMsg("操作成功");
        int tag = request.getTag();
        int pageNum = request.getPageNum();
        if(pageNum == 0){
            pageNum = 1;
        }
        if(tag == 0){
            GetDataReply.Data data = getData(connectionName, connection, database, request.getTable(), pageNum);
            if(data == null){
                data = GetDataReply.Data.newBuilder().setPageNum(1).build();
                return builder.setData(data).build();
            }
            data.toBuilder().setPageNum(request.getPageNum());
            builder.setData(data);
        } else {
            String sql = request.getSql();
            if(StringUtil.isEmpty(sql)){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                builder.setErrCode(704);
                builder.setErrMsg("SQL执行异常，请检查SQL语句");
                return builder.build();
            }
            GetDataReply.Data data = getData(connection, sql);
            if(data == null){
                builder.setErrCode(704);
                builder.setErrMsg("SQL执行异常，请检查SQL语句");
                return builder.build();
            }
            data.toBuilder().setPageNum(1);
            builder.setData(data);
        }
        return builder.build();
    }

    public Connection getConnection(String name){
        return getConnection(name,"mysql");
    }

    public Connection getConnection(String name, String database){
        DatabaseConfig config = databaseConfigDao.findByName(name);
        if(config == null){
            return null;
        }
        String host = config.getHost();
        int port = Integer.valueOf(config.getPort());
        String username = config.getUsername();
        String p = config.getPa();
        Connection connection = DatabaseConnectUtil.connect(host,port,username,p,database);
        return connection;
    }

    public List<String> getDatabases(Connection connection){
        String sql = "select GROUP_CONCAT(SCHEMA_NAME) AS result from information_schema.SCHEMATA";
        String result = exec(connection, sql, null);
        if(result == null){
            return null;
        }
        String[] split = result.split(",");
        List<String> strings = Arrays.asList(split);
        return strings;
    }

    public List<String> getTables(Connection connection, String databaseName){
        List<String> result = new ArrayList<>();
        String sql = "select TABLE_NAME from information_schema.tables where TABLE_SCHEMA = ?";
        Object[] kk = new Object[1];
        kk[0] = databaseName;
        List<Map<String, String>> list = exec1(connection, sql, kk);
        if(list == null){
            return null;
        }
        for(Map<String, String> map : list){
            String s = map.get("TABLE_NAME");
            if(s != null){
                result.add(s);
            }
        }
       return result;
    }

    public GetDataReply.Data getData(Connection connection, String sql){
        GetDataReply.Data.Builder builder = GetDataReply.Data.newBuilder();
        if(sql != null && !sql.contains("select")){
            builder.setTag(2);
            String s = exec2(connection, sql);
            if(s.equals("success")){
                builder.setReason("OK");
            } else {
                builder.setReason("FAILD,"+s);
            }
            return builder.build();
        }
        List<Map<String, String>> list = exec1(connection, sql, null);
        if(list == null){
            return null;
        }
        if(list.size() == 0){
            return builder.build();
        }
        Map<String, String> firstMap = list.get(0);
        Set<String> strings = firstMap.keySet();
        builder.setTag(3);
        builder.setTotal(list.size());
        builder.addAllHeader(strings);
        builder.setPageNum(1);
        for(Map<String,String> map2 : list){
            GetDataReply.Map.Builder builder1 = GetDataReply.Map.newBuilder();
            builder1.putAllMap(map2);
            builder.addValueMaps(builder1);
        }
        return builder.build();
    }

    public GetDataReply.Data getData(String name, Connection connection, String database, String table, int pageNum){
        GetDataReply.Data.Builder builder = GetDataReply.Data.newBuilder();
        int pageSize = 10;
        //默认每页10条数据
        int start = (pageNum - 1)*10;
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from ");
        stringBuffer.append(table);
        stringBuffer.append(" limit ?,?");
        String sql = stringBuffer.toString();
        Object[] kk = new Object[2];
        kk[0] = start;
        kk[1] = pageSize;
        List<Map<String, String>> list = exec1(connection, sql, kk);
        if(list == null){
            return null;
        }
        Connection connection1 = getConnection(name,database);
        if(connection1 == null){
            return null;
        }
        List<String> columnList1 = getColumn(connection1, database, table);
        if(columnList1 == null){
            return null;
        }
        builder.setTag(1);
        builder.addAllHeader(columnList1);
        if(list.size() == 0){
            return builder.build();
        }
        Connection connection2 = getConnection(name,database);
        if(connection2 == null){
            return null;
        }
        int count = getCount(connection2, database, table);
        if(count == 0){
            return builder.build();
        }
        builder.setTotal(count);
        builder.setPageNum(pageNum);
        for(Map<String,String> map2 : list){
            GetDataReply.Map.Builder builder1 = GetDataReply.Map.newBuilder();
            builder1.putAllMap(map2);
            builder.addValueMaps(builder1);
        }
        return builder.build();
    }

    public int getCount(Connection connection, String database, String table){
        String sql = "select TABLE_ROWS from information_schema.tables " +
            "where TABLE_SCHEMA = ? and TABLE_NAME = ?";
        Object[] kk = new Object[2];
        kk[0] = database;
        kk[1] = table;
        Map<String, String> map = exec3(connection, sql, kk);
        if(map == null || map.get("TABLE_ROWS") == null || map.get("TABLE_ROWS").isEmpty()){
            return 0;
        }
        return Integer.valueOf(map.get("TABLE_ROWS"));
    }

    public List<String> getColumn(Connection connection, String database, String table){
        String sql = "select GROUP_CONCAT(COLUMN_NAME) AS result from information_schema.columns " +
            "where TABLE_SCHEMA = ? and TABLE_NAME = ?";
        Object[] kk = new Object[2];
        kk[0] = database;
        kk[1] = table;
        String result = exec(connection, sql, kk);
        if(result == null){
            return null;
        }
        String[] split = result.split(",");
        List<String> strings = Arrays.asList(split);
        return strings;
    }

    public String exec(Connection connection, String sql, Object[] kk){
        JdbcUtil jdbcUtil = new JdbcUtil(connection);
        Map<String, String> map = jdbcUtil.queryForMap(sql, kk);
        if(map == null || map.get("result") == null){
            return null;
        }
        return map.get("result");
    }

    public List<Map<String, String>> exec1(Connection connection, String sql, Object[] kk){
        JdbcUtil jdbcUtil = new JdbcUtil(connection);
        List<Map<String, String>> list = jdbcUtil.queryForList_Map(sql, kk);
        return list;
    }

    public String exec2(Connection connection, String sql){
        JdbcUtil jdbcUtil = new JdbcUtil(connection);
        boolean update = jdbcUtil.update(sql, null);
        if(update){
            return "success";
        }
        return "sql exeception";
    }

    public Map<String, String> exec3(Connection connection, String sql, Object[] kk){
        JdbcUtil jdbcUtil = new JdbcUtil(connection);
        Map<String, String> map = jdbcUtil.queryForMap(sql, kk);
        return map;
    }

    public static void safeClose(Connection stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
    }


}
